package main

import (
	"database/sql/driver"
	"errors"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	"strings"
)

var db *sqlx.DB

func initDB() (err error) {
	dsn := "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True"
	//也可以使用 MustConnect 连接不成功直接就 panic，而 Connect 会返回 err 让我们自己去处理错误
	db, err = sqlx.Connect("mysql", dsn) //Connect 默认就是 Open() + Ping()
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	return
}

type User struct {
	ID   int    `db:"id"`
	Age  int    `db:"age"`
	Name string `db:"name"`
}

// 查询单条数据示例
func queryRowDemo() {
	sqlStr := "select id, name, age from users where id = ?"
	u := &User{}
	err := db.Get(u, sqlStr, 1) //获取 id 为 1 的数据
	if err != nil {
		fmt.Printf("Get failed, err:%v\n", err)
		return
	}
	fmt.Printf("id:%d name:%s age:%d\n", u.ID, u.Name, u.Age)
}

// 查询多条数据示例
func queryMultiRowDemo() {
	sqlStr := "select id, name, age from users where id > ?"
	var users []*User                   // 或者 []User 类型
	err := db.Select(&users, sqlStr, 0) //获取 id 大于 0 的数据
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	fmt.Printf("users:%#v\n", users)
}

// 插入数据
func insertRowDemo() {
	sqlStr := "insert into users(name, age) values (?,?)" //永远都不要自己拼接 SQL 语句！！！！！！！！！！！！！！！！！
	ret, err := db.Exec(sqlStr, "草原小王子", 18)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId() //新插入数据的 id
	if err != nil {
		fmt.Printf("get lastInsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}

// 更新数据
func updateRowDemo() {
	sqlStr := "update users set age = ? where id = ?"
	ret, err := db.Exec(sqlStr, 1500, 3)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() //操作影响的行数
	if err != nil {
		fmt.Printf("get rowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}

// 删除数据
func deleteRowDemo() {
	sqlStr := "delete from users where id = ?"
	ret, err := db.Exec(sqlStr, 1)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() //操作影响的行数
	if err != nil {
		fmt.Printf("get rowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}

func insertUserDemo() (err error) {
	sqlStr := "insert into users (name, age) values (:name, :age)"
	//DB.NamedExec 方法用来绑定 SQL 语句中与 结构体 或 map 中的同名字段
	_, err = db.NamedExec(sqlStr,
		map[string]interface{}{
			"name": "工藤新一",
			"age":  18,
		})
	return
}

func namedQuery() {
	sqlStr := "select * from users where name = :name"
	// 使用map做命名查询
	rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "工藤新一"})
	if err != nil {
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next() {
		var u User
		err = rows.StructScan(&u) //直接将扫描出的结果放到一个 结构体中
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}

	fmt.Println("-------------------------------------------------------------------------------------------------")

	u := User{
		Name: "工藤新一",
	}
	// 使用结构体命名查询，根据结构体字段的 db tag进行映射
	rows, err = db.NamedQuery(sqlStr, u)
	if err != nil {
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next() {
		var u User
		err = rows.StructScan(&u)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}
}

// 事务操作
func transactionDemo2() (err error) {
	/*
		对于事务操作，我们可以使用 sqlx 中提供的 db.Beginx() 和 tx.Exec()。
	*/
	tx, err := db.Beginx() //开启事务
	if err != nil {
		fmt.Printf("begin trans failed, err:%v\n", err)
		return err
	}
	defer func() {
		if p := recover(); p != nil { //recover() 捕获当前函数可能会出现的 panic
			tx.Rollback() //先回滚
			panic(p)      //再 panic
		} else if err != nil { //当前函数是错误的
			fmt.Println("rollback")
			tx.Rollback() //回滚
		} else {
			err = tx.Commit() //提交
			fmt.Println("commit")
		}
	}()

	sqlStr1 := "update users set age = ? where id = ?"

	rs, err := tx.Exec(sqlStr1, 18, 1)
	if err != nil {
		return err
	}
	n, err := rs.RowsAffected()
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}

	sqlStr2 := "Update users set age=? where id=?"
	rs, err = tx.Exec(sqlStr2, 500, 3)
	if err != nil {
		return err
	}
	n, err = rs.RowsAffected()
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}
	return err

}

// Value 使用sqlx.In实现批量插入, 前提是需要我们的结构体实现 driver.Valuer 接口
func (u User) Value() (driver.Value, error) {
	return []interface{}{u.Name, u.Age}, nil
}

// BatchInsertUsers2 使用sqlx.In帮我们拼接语句和参数, 注意传入的参数是[]interface{}
func BatchInsertUsers2(users []interface{}) error {
	query, args, _ := sqlx.In(
		"INSERT INTO users (name, age) VALUES (?), (?), (?)",
		users..., // 如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它
	)
	fmt.Println(query) // 查看生成的querystring
	fmt.Println(args)  // 查看生成的args
	_, err := db.Exec(query, args...)
	return err
}

// BatchInsertUsers3 使用NamedExec实现批量插入
func BatchInsertUsers3(users []*User) error {
	_, err := db.NamedExec("INSERT INTO users (name, age) VALUES (:name, :age)", users)
	return err
}

// QueryByIDs 根据给定ID查询
func QueryByIDs(ids []int) (users []User, err error) {
	// 动态填充id
	query, args, err := sqlx.In("SELECT id, name, age FROM users WHERE id IN (?)", ids)
	if err != nil {
		return
	}
	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	query = db.Rebind(query)

	err = db.Select(&users, query, args...)
	return
}

// QueryAndOrderByIDs 按照指定id查询并维护顺序
func QueryAndOrderByIDs(ids []int) (users []User, err error) {
	// 动态填充id
	strIDs := make([]string, 0, len(ids))
	for _, id := range ids {
		strIDs = append(strIDs, fmt.Sprintf("%d", id))
	}
	query, args, err := sqlx.In("SELECT id, name, age FROM users WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, strings.Join(strIDs, ","))
	if err != nil {
		return
	}

	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
	query = db.Rebind(query)

	err = db.Select(&users, query, args...)
	return
}

func main() {
	if err := initDB(); err != nil {
		fmt.Printf("Init DB failed, err:%v\n", err)
		return
	}
	fmt.Println("Init DB success...")
	//queryRowDemo()
	//queryMultiRowDemo()
	//insertUserDemo()
	//namedQuery()
	//err := transactionDemo2()
	//if err != nil {
	//	fmt.Println(err)
	//}
	//user1 := &User{
	//	Age:  8,
	//	Name: "吉田步美",
	//}
	//
	//user2 := &User{
	//	Age:  8,
	//	Name: "小岛元太",
	//}
	//
	//user3 := &User{
	//	Age:  18,
	//	Name: "白马探",
	//}
	////users := []interface{}{user1, user2, user3}
	////err := BatchInsertUsers2(users)
	////if err != nil {
	////	fmt.Println(err)
	////}
	//users := []*User{user1, user2, user3}
	//err := BatchInsertUsers3(users)
	//if err != nil {
	//	fmt.Println(err)
	//}
	ids := []int{10, 9, 8, 7, 6, 5, 4, 3, 2, 1}
	//users, err := QueryByIDs(ids)
	//if err != nil {
	//	fmt.Println(err)
	//}
	//for _, user := range users {
	//	fmt.Printf("%v\n", user)
	//}
	users, err := QueryAndOrderByIDs(ids)
	if err != nil {
		fmt.Println(err)
	}
	for _, user := range users {
		fmt.Printf("%v\n", user)
	}
}
